package br.cefet.farmaciaonline.dao;

import java.io.ByteArrayOutputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import br.cefet.farmaciaonline.connection.ConnectionFactory;
import br.cefet.farmaciaonline.vo.Medicamento;
import br.cefet.farmaciaonline.vo.Paciente;


public class MedicamentoDAO{
	Connection con = null;
	PreparedStatement ptmt = null;
	ResultSet rs = null;
	private static MedicamentoDAO medicamentoDao;
	
	
	public static final String SELECT_IMAGEM_MEDICAMENTO = " SELECT id_medicamento, imagem FROM medicamento WHERE id_medicamento = ?";
	/*
	 * public EmployeeJdbcDAO() {
	 * 
	 * }
	 */
	
	public static MedicamentoDAO getInstance(){
		
		if (medicamentoDao == null){
			medicamentoDao = new MedicamentoDAO();
		}
		
		return medicamentoDao;
		
	}
	
	public Medicamento getById(int id){
		//boolean result = false;
		Medicamento medicamento = null;
		
		try{
			Connection conn = getConnection();		              
			if(conn != null)  {
			System.out.println("xxxxx");
			String querystring = "SELECT id_medicamento, nome, preco, descricao, principioAtivo FROM medicamento WHERE id_medicamento = ?";
			ptmt = conn.prepareStatement(querystring);
			ptmt.setLong(1, id);
			rs = ptmt.executeQuery();
			if(rs.next()) {	
				System.out.println("yyyyyyy");	
			
				medicamento = new Medicamento();
							
				medicamento.setDescricao(rs.getString("descricao"));
				medicamento.setIdMedicamento(rs.getInt("id_medicamento"));
				medicamento.setNome(rs.getString("nome"));
				medicamento.setPreco(rs.getDouble("preco"));
				medicamento.setPrincipioAtivo(rs.getString("principioAtivo"));		
				
				System.out.println(medicamento.getNome());
			
			}
			
			rs.close();
			}
			
			}catch(Exception e) {
			e.printStackTrace();
			}				
			
			return medicamento;
	}
	
	public Medicamento getImagemById(int idMedicamento){
		boolean result = false;
		Medicamento medicamento = null;
		
		try{
			Connection conn = getConnection();		              
			if(conn != null)  {
				ptmt = conn.prepareStatement(SELECT_IMAGEM_MEDICAMENTO);
				ptmt.setInt(1, idMedicamento);
				ResultSet rst = ptmt.executeQuery();
			if(rst.next()) {	
			
				medicamento = new Medicamento();
							
				medicamento.setIdMedicamento(rst.getInt("id_medicamento"));
				
				//ByteArrayInputStream bais = new ByteArrayInputStream(rst.getBlob("imagem").getBinaryStream());
				byte[] buff = new byte[1024];
				ByteArrayOutputStream baos = new ByteArrayOutputStream();
				
				InputStream is = rst.getBlob("imagem").getBinaryStream();
				int i = is.read(buff);
				while (i > 0) {
					baos.write(buff,0,i);
					i = is.read(buff);
				}
							
				
				medicamento.setImagem(baos.toByteArray());
			}
			
			rst.close();
			}
			
			}catch(Exception e) {
			e.printStackTrace();
			}	
			
			
			return medicamento;
	}
	
	private ResultSet executePreparedStatement(Connection conn,
			String selectImagemMedicamento, int idMedicamento) {
		// TODO Auto-generated method stub
		return null;
	}

	public List<Medicamento> pesquisa(String param){
		List<Medicamento> medicamentos = new ArrayList<Medicamento>();
		Medicamento medicamento = null;
		try {
			String querystring = "SELECT * FROM medicamento WHERE nome like ? ";
			con = getConnection();
			ptmt = con.prepareStatement(querystring);
			ptmt.setString(1, "%" + param + "%");
			
			rs = ptmt.executeQuery();
			while (rs.next()) {
				 medicamento=new Medicamento();
				 medicamento.setIdMedicamento(rs.getInt(1));
				 medicamento.setNome(rs.getString(2));
				 medicamento.setPreco(rs.getDouble(3));
				 medicamento.setDescricao(rs.getString(4));
				 medicamento.setPrincipioAtivo(rs.getString(5));
				 medicamento.setImagem(rs.getBytes(6));
				 medicamentos.add(medicamento);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (ptmt != null)
					ptmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return medicamentos;
	}
	
	private Connection getConnection() throws SQLException {
		Connection conn;
		conn = ConnectionFactory.getInstance().getConnection();
		return conn;
	}

	public void inserir(Medicamento medicamento){
		
		try {
			String querystring = "INSERT INTO medicamento  " +
			"( nome, preco, descricao, principioAtivo, imagem ) " +
			"VALUES (?,?,?,?,?)";
			Connection conn = getConnection();		              
			conn = getConnection();
			ptmt = conn.prepareStatement(querystring);

			ptmt.setString(1, medicamento.getNome()); 
			ptmt.setDouble(2, medicamento.getPreco()); 
			ptmt.setString(3, medicamento.getDescricao());
			ptmt.setString(4, medicamento.getPrincipioAtivo());
			ptmt.setBytes(5, medicamento.getImagem());

			 
			  ptmt.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (ptmt != null)
					ptmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}
	}
	
	public void add(Paciente paciente) {

		try {
			String querystring = "INSERT INTO EMPLOYEE VALUES(?,?,?,?,?)";
			con = getConnection();
			ptmt = con.prepareStatement(querystring);
			/*
			 * ptmt.setString(1, String.valueOf(System.currentTimeMillis()));
			 * ptmt.setString(2, paciente.getEmpName()); ptmt.setString(3,
			 * paciente.getEmpAddr()); ptmt.setString(4,
			 * paciente.getEmpEmail()); ptmt.setString(5,
			 * paciente.getEmpPhone());
			 */
			ptmt.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (ptmt != null)
					ptmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}

	}

	public void update(Paciente paciente) {

		try {
			String querystring = "UPDATE EMPLOYEE SET EMP_NAME=?,EMP_ADDR=?,"
					+ "EMP_EMAIL=?,EMP_PHONE=? WHERE EMP_ID=?";
			con = getConnection();
			ptmt = con.prepareStatement(querystring);

			/*
			 * ptmt.setString(1, employeeBean.getEmpName()); ptmt.setString(2,
			 * employeeBean.getEmpAddr()); ptmt.setString(3,
			 * employeeBean.getEmpEmail()); ptmt.setString(4,
			 * employeeBean.getEmpPhone()); ptmt.setString(5,
			 * employeeBean.getEmpId());
			 */
			ptmt.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (ptmt != null)
					ptmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}

	}

	public void delete(String employeeId) {

		try {
			String querystring = "DELETE FROM EMPLOYEE WHERE EMPID=?";
			con = getConnection();
			ptmt = con.prepareStatement(querystring);
			ptmt.setString(1, employeeId);
			ptmt.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (ptmt != null)
					ptmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}

	}

	public List<Paciente> findAll() {
		List<Paciente> pacientes = new ArrayList<Paciente>();
		Paciente paciente = null;
		try {
			String querystring = "SELECT * FROM EMPLOYEE";
			con = getConnection();
			ptmt = con.prepareStatement(querystring);
			rs = ptmt.executeQuery();
			while (rs.next()) {
				paciente = new Paciente();
				/*
				 * employeeBean.setEmpId(rs.getString(1)); Trocar por paciente
				 * employeeBean.setEmpName(rs.getString(2));
				 * employeeBean.setEmpAddr(rs.getString(3));
				 * employeeBean.setEmpEmail(rs.getString(4));
				 * employeeBean.setEmpPhone(rs.getString(5));
				 */

				pacientes.add(paciente);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (ptmt != null)
					ptmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}
		return pacientes;
	}

	public Paciente findByPrimaryKey(String empId) {

		Paciente paciente = null;
		try {
			String querystring = "SELECT * FROM EMPLOYEE WHERE EMP_ID=?";
			con = getConnection();
			ptmt = con.prepareStatement(querystring);
			ptmt.setString(1, empId);
			rs = ptmt.executeQuery();
			if (rs.next()) {
				/*
				 * employeeBean=new EmployeeBean(); trocar por paciente
				 * employeeBean.setEmpId(rs.getString(1));
				 * employeeBean.setEmpName(rs.getString(2));
				 * employeeBean.setEmpAddr(rs.getString(3));
				 * employeeBean.setEmpEmail(rs.getString(4));
				 * employeeBean.setEmpPhone(rs.getString(5));
				 */
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (ptmt != null)
					ptmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}
		return paciente;
	}

	public static void main(String[] args) {

		PacienteDAO employeeDAO = new PacienteDAO();
		System.out.println(employeeDAO.findAll().size());
	}
}